# -*- coding: utf-8 -*-


import pymysql

db_map = {}
key = "db_41"

arg_kwargs = {
    'host': "127.0.0.1",
    'port': 3306,
    'user': 'root',
    'password': "root",
    'database': "pubmed",
    'charset': 'utf8'
}


def get_connection():
    if db_map.get(key) is None:
        db = pymysql.connections.Connection(**arg_kwargs)
        db_map[key] = db
        print("get db from pymysql")
        return db
    #print("get db from db_map")
    return db_map.get(key)


def execute(sql, data):
    db = get_connection()
    cur = db.cursor()
    try:
        cur.executemany(sql, data)
    except Exception as e:
        print("execute sql error, sql:", sql, e)
        db.rollback()
    else:
        db.commit()
    finally:
        cur.close()


def execute_query(sql):
    db = get_connection()
    cur = db.cursor()
    try:
        cur.execute(sql)
        result = cur.fetchall()
        return result
    except Exception as e:
        print("execute query error, sql:<{}>", sql, e)
    finally:
        cur.close()


def insert_article_node(data):
    '''
    data=[('数学','张三','机械出版社',78,"2023-06-04","数学书"),('英语','李四','机械出版社',67,"2023-07-04","英语书"),('活着','余华','人民出版社',46,"2023-06-01","富贵的一生")]
    :param data:
    :return:
    '''
    sql = ("insert ignore into article (`pmid`,`doi`,`pii`,`pmc`,`article_title`,`medline_pgn`,`publication_status`,"
           "`language`,`vernacular_title`,`abstract`,`citation`) values (%s, %s, %s,%s, %s, %s, %s,%s, %s, %s,%s)")
    execute(sql, data)


def insert_journal_node(data):
    sql = ("insert ignore into journal (`id`,`issn`,`title`,`iso_abbreviation`,`volume`,`issue`,`pub_date`) values ("
           "%s, %s, %s,%s, %s, %s, %s)")
    execute(sql, data)


def insert_article2journal_relation(data):
    '''
    写入article2journal关系数据
    :param data:
    :return:
    '''
    sql = "insert into rel_article_journal (`article_id`, `journal_id`, `volume_issue`) values (%s, %s, %s)"
    execute(sql, data)


def insert_medline_journal_info_node(data):
    '''
    先查再写
    :param data:
    :return:
    '''
    sql = ("insert ignore into medline_journal_info (`id`,`nlm_unique_id`,`country`,`medline_ta`,`issn_linking`) values (%s, "
           "%s, %s,%s, %s)")
    execute(sql, data)


def insert_medline2journal_relation(data):
    sql = "insert into rel_medline_journal (`medline_id`, `journal_id`) values (%s, %s)"
    execute(sql, data)


def insert_medline2article_relation(data):
    sql = "insert into rel_article_medline (`pmid`, `medline_id`) values (%s, %s)"
    execute(sql, data)


def insert_mesh_word_node(data):
    '''
    主题词   先查再写
    :param data:
    :return:
    '''
    sql = "insert ignore into mesh_word (`mesh_word_id`, `descriptor_name`, `qualifier_name`) values (%s, %s, %s)"
    execute(sql, data)


def insert_mesh_word2article_relation(data):
    sql = "insert into rel_mesh_word_article (`article_id`, `mesh_word_id`) values (%s, %s)"
    execute(sql, data)


def insert_chemical_node(data):
    '''
    化学品词   先查再写
    :param data:
    :return:
    '''
    sql = "insert ignore into chemical (`chemical_id`, `name_of_substance`, `registry_number`) values (%s, %s, %s)"
    execute(sql, data)


def insert_article2chemical_relation(data):
    sql = "insert into rel_article_chemical (`article_id`, `chemical_id`) values (%s, %s)"
    execute(sql, data)


def insert_publication_type_node(data):
    '''
    出版类型  先查后写
    :param data:
    :return:
    '''
    sql = "insert ignore into publication_type (`id`, `name`) values (%s, %s)"
    execute(sql, data)


def insert_article2publication_type_relation(data):
    sql = "insert into rel_article_publication_type (`article_id`, `publication_type_id`) values (%s, %s)"
    execute(sql, data)


def insert_author_node(data):
    '''
    写入作者数据
    :param data:
    :return:
    '''
    sql = "insert ignore into author (`author_id`, `name`, `Initials`) values (%s, %s, %s)"
    execute(sql, data)


def insert_article2author_relation(data):
    sql = "insert into rel_article_author (`article_id`, `author_id`) values (%s, %s)"
    execute(sql, data)


def insert_reference_node(data):
    '''
    参考文献  合并至article表
    :param data:
    :return:
    '''
    sql = "insert ignore into reference (`pmid`, `title`, `citation`) values (%s, %s, %s)"
    execute(sql, data)


def insert_article2reference_relation(data):
    sql = "insert into rel_article_reference (`article_id`, `reference_article_id`, `citation`) values (%s, %s, %s)"
    execute(sql, data)


def find_max_id(table_name):
    sql = 'SELECT MAX(id) FROM ' + table_name
    results = execute_query(sql)
    for row in results:
        print('{0} max id:'.format(row[0]))
        return row[0]
    return 0


def close():
    db = get_connection()

    db.close()


if __name__ == '__main__':
    find_max_id('author')
